ALTER PROCEDURE [dbo].[spAPI_Promotion_Submit]
(
	@Token VARCHAR(255),
	@PromotionId INT
)
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @IdResult INT
	DECLARE @BusinessId INT
	
	DECLARE @CampaignBudget MONEY
	DECLARE @CreditCardNo VARCHAR(20)	
	
	SET @IdResult = 0
	EXEC spAPI_Business_CheckToken @Token = @Token, @Result = @IdResult OUT, @BusinessId = @BusinessId OUT
	
	IF @IdResult = 1
		IF EXISTS(SELECT 1 FROM Promotion (NOLOCK) WHERE BusinessId = @BusinessId AND PromotionId = @PromotionId)
		BEGIN
			UPDATE Promotion
			SET
				Ready = 1,
				PromotionNumber = (select isnull(max(PromotionNumber) + 1, 1) from Promotion (nolock) WHERE BusinessId = @BusinessId),
				CheckinCode = CONVERT (VARCHAR(4), (1000+ ABS(CHECKSUM(NewId())) % 8999)) 
			WHERE (PromotionId = @PromotionId)
			
			EXEC spPromotion_StartStop @PromotionId = @PromotionId
			
			SELECT @CampaignBudget = ISNULL(CampaignBudget, 0)
			FROM Promotion (NOLOCK)
			WHERE (PromotionId = @PromotionId)
			
			-- Temporary write to pay
			SELECT @CreditCardNo = CreditCardNo
			FROM Business (NOLOCK)
			WHERE (BusinessId = @BusinessId)
						
			IF @CampaignBudget > 0
				INSERT INTO PromotionPay
				(PromotionId, DatePay, Amount, CreditCardNo)
				VALUES (@PromotionId, GETUTCDATE(), @CampaignBudget, @CreditCardNo)
			----
		END	
		ELSE
			SET @IdResult = -1

	SELECT @IdResult AS IdResult
END
GO
ALTER PROCEDURE [dbo].[spPromotion_StartStop]
(
	@PromotionId INT = NULL
)
AS
BEGIN
	SET NOCOUNT ON
	
	DECLARE @Now DATETIME
	SET @Now = GETUTCDATE()
	
	CREATE TABLE #tmpPromotion1
	(
		PromotionId INT
	)
	
	CREATE TABLE #tmpPromotion2
	(
		PromotionId INT
	)
	
	IF @PromotionId IS NOT NULL
		INSERT INTO #tmpPromotion1
		(PromotionId)
		SELECT PromotionId
		FROM Promotion (NOLOCK)
		WHERE (PromotionId = @PromotionId)
			AND (StatusCode = 'PEN')
			AND (Deleted = 0)
			AND (Ready = 1)
			AND (BaseCostTimingId IS NOT NULL)
	ELSE
		INSERT INTO #tmpPromotion1
		(PromotionId)
		SELECT PromotionId
		FROM Promotion (NOLOCK)
		WHERE (StatusCode = 'PEN')
			AND (Deleted = 0)
			AND (Ready = 1)
			AND (BaseCostTimingId IS NOT NULL)
			
	-- Starts promotion
	UPDATE Promotion
	SET StatusCode = 'ACT'
	FROM #tmpPromotion1
	WHERE (Promotion.PromotionId = #tmpPromotion1.PromotionId)
		AND (
				-- 2, 4 - starts now
				(Promotion.BaseCostTimingId IN (2, 4)) OR 
				-- 3 - shedule at
				((Promotion.BaseCostTimingId = 3) AND (Promotion.BaseCostTimingScheduledAt <= @Now)) OR
				-- 5 - shedule date range
				((Promotion.BaseCostTimingId = 5) AND (Promotion.BaseCostTimingStarts <= @Now))
			)
			
	-- Make pay for starts promotion
	INSERT INTO PromotionCost
	(PromotionId, DateSubmit, FixedCost)
	SELECT Promotion.PromotionId, GETUTCDATE(), Promotion.FixedCost
	FROM Promotion (NOLOCK) INNER JOIN #tmpPromotion1 ON Promotion.PromotionId = #tmpPromotion1.PromotionId
	WHERE -- 2, 4 - starts now
		(Promotion.BaseCostTimingId IN (2, 4)) OR 
		-- 3 - shedule at
		((Promotion.BaseCostTimingId = 3) AND (Promotion.BaseCostTimingScheduledAt <= @Now)) OR
		-- 5 - shedule date range
		((Promotion.BaseCostTimingId = 5) AND (Promotion.BaseCostTimingStarts <= @Now))
				
	IF @PromotionId IS NOT NULL
		INSERT INTO #tmpPromotion2
		(PromotionId)
		SELECT PromotionId
		FROM Promotion (NOLOCK)
		WHERE (PromotionId = @PromotionId)
			AND (StatusCode = 'ACT')
			AND (Deleted = 0)
			AND (Ready = 1)
			AND (BaseCostTimingId IS NOT NULL)
	ELSE
		INSERT INTO #tmpPromotion2
		(PromotionId)
		SELECT PromotionId
		FROM Promotion (NOLOCK)
		WHERE (StatusCode = 'ACT')
			AND (Deleted = 0)
			AND (Ready = 1)
			AND (BaseCostTimingId IS NOT NULL)
	
	-- Stop promotion
	UPDATE Promotion
	SET StatusCode = 'COM'			
	FROM #tmpPromotion2
	WHERE (Promotion.PromotionId = #tmpPromotion2.PromotionId)
		AND (Promotion.BaseCostTimingId = 5) 
		AND (Promotion.BaseCostTimingEnds <= @Now)

	DROP TABLE #tmpPromotion1
	DROP TABLE #tmpPromotion2
END
GO
ALTER PROCEDURE [dbo].[spAPI_Business_GetPromotionDetails]
(
	@Token VARCHAR(255),
	@PromotionId INT
)
AS
BEGIN
	SET NOCOUNT ON
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
	DECLARE @IdResult INT
	DECLARE @BusinessId INT
	
	DECLARE @Result XML
	
	SET @IdResult = 0
	EXEC spAPI_Business_CheckToken @Token = @Token, @Result = @IdResult OUT, @BusinessId = @BusinessId OUT
	
	IF @IdResult = 1
		IF EXISTS(SELECT 1 FROM Promotion WHERE BusinessId = @BusinessId AND PromotionId = @PromotionId)
		BEGIN
			SELECT @Result = (SELECT 
									Promotion.[PromotionId],
									Promotion.[BusinessId],
									Promotion.[ProximityRadiusId],
									pr.FromRadius as ProximityFromRadius,
									pr.ToRadius as ProximityToRadius,
									Promotion.[CustomRange],
									Promotion.[Over200MilesFlag],
									Promotion.[RegisteredOutsideOfZip],
									Promotion.[RegisteredOutsideOfState],
									Promotion.[RegisteredOutsideOfCity],
									Promotion.[BaseCostTimingId],
									bct.Timing as BaseCostTimingName,
									Promotion.[BaseCostTimingScheduledAt],
									Promotion.[BaseCostTimingStarts],
									Promotion.[BaseCostTimingEnds],
									Promotion.[CampaignBudget],
									Promotion.[Name],
									Promotion.[PromotionText],
									Promotion.[PromixityRadiusFixedCost],
									Promotion.[PromixityRadiusRatePerPush],									
									Promotion.[CostTimingFixedCost],
									Promotion.[CostTimingRatePerPush],
									Promotion.BaseCostTimingRatePerPush,
									Promotion.BaseCostTimingFixedCost,
									Promotion.[StatusCode],
									Promotion.[FixedCost],
									Promotion.[RatePerPush],
									Promotion.[CheckinCode],
									Promotion.[Deleted],
									Promotion.[Ready],									
									ISNULL(vwCheckin.CheckIns, 0) AS CheckIns,
									CONVERT(VARCHAR(20), ISNULL(vwReview.AvgRating, 0)) + ',' + 
									CASE WHEN ISNULL(vwReview.AvgRating, 0) BETWEEN 0 AND 2 THEN 'Y' 
										WHEN ISNULL(vwReview.AvgRating, 0) BETWEEN 3 AND 4 THEN 'G' 
									ELSE 'R' END AS Rating,
									ISNULL(vwReview.CountReviews, 0) AS Reviews,
																		
									CASE WHEN (Promotion.RatePerPush = 0) THEN 0
									ELSE CONVERT(INT, FLOOR((Promotion.CampaignBudget - Promotion.FixedCost) / Promotion.RatePerPush)) 
									END AS MaxPushes,
									
									ISNULL(vwPushes.Amount, 0) + ISNULL(vwPromotionCost.Amount, 0) AS SpentMoney, 
									ISNULL(vwPushes.CountPushes, 0) AS SpentPushes,
									ISNULL(vwPay.Amount, 0) - ISNULL(vwPushes.Amount, 0) - ISNULL(vwPromotionCost.Amount, 0) AS UnusedMoney,
									
									-- Max pushes - Spent Pushes
									CASE WHEN (Promotion.RatePerPush = 0) THEN 0
									ELSE CONVERT(INT, FLOOR((Promotion.CampaignBudget - Promotion.FixedCost) / Promotion.RatePerPush)) 
									END - ISNULL(vwPushes.CountPushes, 0) AS UnusedPushes,
									
									case
										when Promotion.[BaseCostTimingId] = 5 then abs(datediff(minute, getutcdate(), Promotion.[BaseCostTimingEnds]))
										else -1 end as MinutesLeft,
										
									(SELECT Category.CategoryId AS Id, Category.CategoryName AS Name,
											dbo.fnAPI_Category_Path(Category.CategoryId) AS FullPath
										FROM PromotionCategory LEFT JOIN Category ON PromotionCategory.CategoryId = Category.CategoryId
										WHERE PromotionCategory.PromotionId = Promotion.PromotionId
										FOR XML AUTO, TYPE) AS Categories
								FROM Promotion LEFT JOIN BaseCostTiming bct on bct.BaseCostTimingId = Promotion.BaseCostTimingId
											LEFT JOIN ProximityRadius pr on pr.ProximityRadiusId = Promotion.ProximityRadiusId
											LEFT JOIN (SELECT BusinessId, 
															COUNT(*) AS CountReviews, ROUND(AVG(CONVERT(FLOAT, Rating)), 0) AS AvgRating 
														FROM BusinessReview
														GROUP BY BusinessId) AS vwReview ON Promotion.BusinessId = vwReview.BusinessId,
											
									(SELECT COUNT(*) AS CountPushes, SUM(RatePerPush) AS Amount
									FROM PromotionPush
									WHERE PromotionId = @PromotionId) AS vwPushes,
									
									(SELECT SUM(Amount) AS Amount
									FROM PromotionPay
									WHERE PromotionId = @PromotionId) AS vwPay,
									
									(SELECT SUM(FixedCost) AS Amount
									FROM PromotionCost
									WHERE PromotionId = @PromotionId) AS vwPromotionCost,
																		
									(SELECT COUNT(*) AS CheckIns 
									FROM PromotionCheckin 
									WHERE PromotionId = @PromotionId) AS vwCheckin
												
								WHERE (Promotion.PromotionId = @PromotionId)
								FOR XML PATH ('Promotion'))
		END								
		ELSE
			SET @IdResult = -1
	
	SELECT @IdResult AS IdResult, ISNULL(@Result, '') AS Result
END
GO
ALTER PROCEDURE [dbo].[spAPI_User_GetPromotionDetails]
(
	@Token VARCHAR(255),
	@PromotionId INT
)
AS
BEGIN
	SET NOCOUNT ON
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
	DECLARE @IdResult INT
	DECLARE @UserId INT
	
	DECLARE @Result XML
	
	SET @IdResult = 0
	EXEC spAPI_User_CheckToken @Token = @Token, @Result = @IdResult OUT, @UserId = @UserId OUT
	
	IF @IdResult = 1
		IF EXISTS(SELECT 1 FROM PromotionPush WHERE UserId = @UserId AND PromotionId = @PromotionId)
		BEGIN
			SELECT @Result = (SELECT 
									Promotion.[PromotionId],
									Promotion.[BusinessId],
									Promotion.[ProximityRadiusId],
									pr.FromRadius as ProximityFromRadius,
									pr.ToRadius as ProximityToRadius,
									Promotion.[CustomRange],
									Promotion.[Over200MilesFlag],
									Promotion.[RegisteredOutsideOfZip],
									Promotion.[RegisteredOutsideOfState],
									Promotion.[RegisteredOutsideOfCity],
									Promotion.[BaseCostTimingId],
									bct.Timing as BaseCostTimingName,
									Promotion.[BaseCostTimingScheduledAt],
									Promotion.[BaseCostTimingStarts],
									Promotion.[BaseCostTimingEnds],
									Promotion.[CampaignBudget],
									Promotion.[Name],
									Promotion.[PromotionText],
									Promotion.[PromixityRadiusFixedCost],
									Promotion.[PromixityRadiusRatePerPush],									
									Promotion.[CostTimingFixedCost],
									Promotion.[CostTimingRatePerPush],
									Promotion.BaseCostTimingRatePerPush,
									Promotion.BaseCostTimingFixedCost,
									Promotion.[StatusCode],
									Promotion.[FixedCost],
									Promotion.[RatePerPush],
									Promotion.[CheckinCode],
									Promotion.[Deleted],
									Promotion.[Ready],									
									ISNULL(vwCheckin.CheckIns, 0) AS CheckIns,
									
									CASE WHEN (Promotion.RatePerPush = 0) THEN 0
									ELSE CONVERT(INT, FLOOR((Promotion.CampaignBudget - Promotion.FixedCost) / Promotion.RatePerPush)) 
									END AS MaxPushes,
									
									ISNULL(vwPushes.Amount, 0) + ISNULL(vwPromotionCost.Amount, 0) AS SpentMoney, 
									ISNULL(vwPushes.CountPushes, 0) AS SpentPushes,
									ISNULL(vwPay.Amount, 0) - ISNULL(vwPushes.Amount, 0) - ISNULL(vwPromotionCost.Amount, 0) AS UnusedMoney,
									
									-- Max pushes - Spent Pushes
									CASE WHEN (Promotion.RatePerPush = 0) THEN 0
									ELSE CONVERT(INT, FLOOR((Promotion.CampaignBudget - Promotion.FixedCost) / Promotion.RatePerPush)) 
									END - ISNULL(vwPushes.CountPushes, 0) AS UnusedPushes,
									
									case
										when Promotion.[BaseCostTimingId] = 5 then abs(datediff(minute, getdate(), Promotion.[BaseCostTimingEnds]))
										else -1 end as MinutesLeft,
										
									(SELECT Category.CategoryId AS Id, Category.CategoryName AS Name,
											dbo.fnAPI_Category_Path(Category.CategoryId) AS FullPath
										FROM PromotionCategory LEFT JOIN Category ON PromotionCategory.CategoryId = Category.CategoryId
										WHERE PromotionCategory.PromotionId = Promotion.PromotionId
										FOR XML AUTO, TYPE) AS Categories
								FROM Promotion left join BaseCostTiming bct on bct.BaseCostTimingId = Promotion.BaseCostTimingId
												left join ProximityRadius pr on pr.ProximityRadiusId = Promotion.ProximityRadiusId,
												
									(SELECT COUNT(*) AS CountPushes, SUM(RatePerPush) AS Amount
									FROM PromotionPush
									WHERE PromotionId = @PromotionId) AS vwPushes,
									
									(SELECT SUM(Amount) AS Amount
									FROM PromotionPay
									WHERE PromotionId = @PromotionId) AS vwPay,
									
									(SELECT SUM(FixedCost) AS Amount
									FROM PromotionCost
									WHERE PromotionId = @PromotionId) AS vwPromotionCost,
									
									(SELECT COUNT(*) AS CheckIns 
									FROM PromotionCheckin 
									WHERE PromotionId = @PromotionId) AS vwCheckin
												
								WHERE (Promotion.PromotionId = @PromotionId)
								FOR XML PATH ('Promotion'))
		END								
		ELSE
			SET @IdResult = -1
	
	SELECT @IdResult AS IdResult, ISNULL(@Result, '') AS Result
END
GO